<?php
class Model_Reports extends Zend_Db_Table_Abstract {
    public function getRequestsReport($dbeg = -1, $dend = -1, $start = 0, $limit = 0){
        
        $limit = ($limit != 0)?"LIMIT ".$start.", ".$limit:"";
        
        $wdbeg = ($dbeg != -1)?"AND DATE(s.FECHA_SOLICITUD) >= DATE('".$dbeg."')":"";
        $wdend = ($dend != -1)?"AND DATE(s.FECHA_SOLICITUD) <= DATE('".$dend."')":"";
        
        $sql = sprintf("SELECT SQL_CALC_FOUND_ROWS s.NUM_SOLICITUD, date_format(s.FECHA_SOLICITUD, '%%d/%%m/%%Y') as FECHA_SOLICITUD, p.IDPROYECTO, p.NOMPROYECTO, date_format(d.DEL_DELEGATE_DATE, '%%d/%%m/%%Y') as FECHA_APROBACION, ca.IDCARGO, ca.NOMCARGO, s.NUM_VACANTES, s.VALOR_COMPENSACION,
                        tc.IDTIPOCONTRATO, tc.NOMTIPOCONTRATO, COUNT(r.NUM_ID) AS CANDIDATOS_EVALUADOS, COUNT(r1.NUM_ID) AS PRUEBAS_PRESENTADAS, 
                        COUNT(r2.NUM_ID) AS PRUEBAS_APROBADAS, COUNT(r3.NUM_ID) AS PRUEBAS_PENDIENTES, COUNT(r4.NUM_ID) AS CANDIDATOS_CONTRATADOS, 
                        (s.NUM_VACANTES - COUNT(r4.NUM_ID)) AS PENDIENTES
                        FROM SP_SOLICITUD s
                        LEFT JOIN H_RESERVA_CANDIDATOS r ON r.APPLICATION = s.APPLICATION
                        LEFT JOIN H_RESERVA_CANDIDATOS r1 ON r1.APPLICATION = s.APPLICATION AND ISNULL(r1.FECHA_TECNICA) <> 1 AND r1.NUM_ID = r.NUM_ID
                        LEFT JOIN H_RESERVA_CANDIDATOS r2 ON r2.APPLICATION = s.APPLICATION AND ISNULL(r2.FECHA_TECNICA) <> 1 AND r2.APROBO_TECNICA = 'SI' AND r2.NUM_ID = r1.NUM_ID
                        LEFT JOIN H_RESERVA_CANDIDATOS r3 ON r3.APPLICATION = s.APPLICATION AND ISNULL(r3.FECHA_TECNICA) = 1 AND r3.NUM_ID = r2.NUM_ID
                        LEFT JOIN H_RESERVA_CANDIDATOS r4 ON r4.APPLICATION = s.APPLICATION AND ISNULL(r4.FECHA_FORMALIZACION) <> 1 AND r4.NUM_ID = r3.NUM_ID
                        , SP_PROYECTO p, APP_DELEGATION d, SP_CARGO c, SP_TIPO_CONTRATO tc, SP_CARGO ca
                        WHERE p.IDPROYECTO = s.PROYECTO %s %s AND d.APP_UID = s.APPLICATION AND d.DEL_INDEX = 2 AND c.IDCARGO = s.CARGO_VACANTE AND tc.IDTIPOCONTRATO = s.TIPO_CONTRATO
                        AND ca.IDCARGO = s.CARGO_VACANTE
                        GROUP BY s.NUM_SOLICITUD, p.IDPROYECTO, p.NOMPROYECTO, d.DEL_DELEGATE_DATE, s.NUM_VACANTES, s.VALOR_COMPENSACION,
                        tc.IDTIPOCONTRATO, tc.NOMTIPOCONTRATO, ca.IDCARGO, ca.NOMCARGO %s", $wdbeg, $wdend, $limit);     
        
//        echo $sql;
//        die();
        
        $db = $this->getDefaultAdapter();
        $stmt = $db->query($sql);
        $data = $stmt->fetchAll();
        
        $sql = "SELECT FOUND_ROWS()";
        $stmt = $db->query($sql);
        $total = $stmt->fetchColumn();
        
        $resp = array(
            'data' => $data,
            'total' => $total
        );        
        return $resp;
    }
    public function getRequestStatusGroups($dbeg = '-1', $dend = '-1'){
        
        $wdbeg = ($dbeg != '-1')?"AND DATE(s.FECHA_SOLICITUD) >= DATE('".$dbeg."')":"";
        $wdend = ($dend != '-1')?"AND DATE(s.FECHA_SOLICITUD) <= DATE('".$dend."')":"";
        
        
        $sql = sprintf("SELECT g.NUM_SOLICITUD, g.NUM_GRUPO, c.CON_VALUE AS TAS_TITLE, g.NUM_CANDIDATOS
        FROM SP_SOLICITUD s, SP_GRUPO_CANDIDATOS g, APP_DELEGATION d, CONTENT c
        WHERE g.NUM_SOLICITUD = s.NUM_SOLICITUD 
        %s
        %s
        AND d.APP_UID = g.APPLICATION AND d.DEL_INDEX = (
        SELECT MAX(d1.DEL_INDEX)
        FROM APP_DELEGATION d1
        WHERE d1.APP_UID = g.APPLICATION
        ) AND c.CON_CATEGORY = 'TAS_TITLE' AND c.CON_ID = d.TAS_UID AND c.CON_LANG = 'en'
        ORDER BY g.NUM_SOLICITUD, g.NUM_GRUPO", $wdbeg, $wdend);
        
        $db = $this->getDefaultAdapter();
        
        $stmt = $db->query($sql);
        $data = $stmt->fetchAll();
        if(!is_array($data)){
            $data = null;
        }
        return $data;
    }
    public function getRequestMaxGroup($dbeg = '-1', $dend = '-1'){
        
        $wdbeg = ($dbeg != '-1')?"AND DATE(s.FECHA_SOLICITUD) >= DATE('".$dbeg."')":"";
        $wdend = ($dend != '-1')?"AND DATE(s.FECHA_SOLICITUD) <= DATE('".$dend."')":"";
        
        
        $sql = sprintf("SELECT MAX(g.NUM_GRUPO) AS MAX_GROUP
                        FROM SP_SOLICITUD s, SP_GRUPO_CANDIDATOS g, APP_DELEGATION d, CONTENT c
                        WHERE g.NUM_SOLICITUD = s.NUM_SOLICITUD 
                        %s
                        %s
                        AND d.APP_UID = g.APPLICATION AND d.DEL_INDEX = 
                        (
                            SELECT MAX(d1.DEL_INDEX)
                            FROM APP_DELEGATION d1
                            WHERE d1.APP_UID = g.APPLICATION                        
                        ) AND c.CON_CATEGORY = 'TAS_TITLE' AND c.CON_ID = d.TAS_UID AND c.CON_LANG = 'en'
                        ORDER BY g.NUM_SOLICITUD", $wdbeg, $wdend);
        
        $db = $this->getDefaultAdapter();
        
        $stmt = $db->query($sql);
        $maxGrp = $stmt->fetchColumn();
        if(!$maxGrp){
            $maxGrp = null;
        }
        return $maxGrp;
    }
}
?>
